Database queries include constructs referred to as expressions, where an expression is typically a combination of one or more values, operators, and/or functions. During query execution, a database server instance evaluates the query expressions to produce a query result. Query expressions may cause a database server instance to perform a variety of operations such as filtering, projecting, aggregating, sorting, or otherwise manipulating values stored within a database object. Some query expressions require process-intensive operations that consume a significant amount of resources per evaluation. In some cases, these expressions appear across multiple queries or multiple parts of the same query. When these expressions are evaluated over large datasets repeatedly, the performance of a database server instance may be significantly impacted.
One approach that database administrators use to optimize execution of computationally-expensive queries is to create materialized views. A materialized view is a database object that contains the results of a query. For example, a materialized view may capture the final result of two tables joined together. Once the materialized view is created, subsequent queries may be rewritten to access the materialized view rather than performing another table join operation. Thus, materialized views obviate the need for multiple executions of queries that may require significant time to compute.
While materialized views are useful for capturing the final results of a query, materialized views do not capture data that tracks the usage of individual expressions within the query. Further, materialized views do not capture intermediary expression results that reside solely in memory during query execution. For example, a hash join operation computes hash values from a join attribute to determine which records to join from two different tables. A materialized view may store the combined records representing the final result of the join operation. However, the materialized view does not capture data relating to the hash operation since the resultant hash values are not part of the final query result and are not persistently stored within the database. Similarly, the results of other expression evaluations and internal computations that reside only in memory during query execution are not typically captured by a database server.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.